package cn.lix.demo.excel;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ExcelParser {

    /**
     * 匹配正则占位符{}
     */
    private static final Pattern SIGN = Pattern.compile("\\{([A-Za-z]+)\\}");

    public static List<Map<String, String>> getRowValues(MultipartFile file, int startRow) throws IOException {
        startRow = startRow < 1 ? 1 : startRow;
        List<Map<String, String>> parserLine = new ArrayList<>(16);
        InputStream fileInputStream = file.getInputStream();
        // 创建工作簿对象
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        // 获取第一个工作表
        Sheet sheet = workbook.getSheetAt(0);

        // 遍历行(startRow + 1是因为excel行从1开始，而读取数据是从0开始，所以excel的1对应的是下标为0的数据)
        for (int i = (startRow - 1); i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            Map<String, String> m = new HashMap<>();
            // 遍历单元格
            for (Cell cell : row) {
                // 获取单元格所在的列索引
                String column = convertToExcelColumn(cell.getColumnIndex());
                m.put(column, getStringValue(cell, evaluator));
            }
            parserLine.add(m);
        }

        // 关闭文件输入流
        fileInputStream.close();
        workbook.close();
        return parserLine;
    }


    public static String fixTemplate(String template, List<Map<String, String>> parserLine) {
        StringBuffer stringBuffer = new StringBuffer(64);
        for (Map<String, String> stringMap : parserLine) {
            Matcher matcher = SIGN.matcher(template);

            while (matcher.find()) {
                String placeholder = matcher.group(1);
                String replacement = stringMap.get(placeholder);
                matcher.appendReplacement(stringBuffer, replacement == null ? "" : replacement);
            }
            matcher.appendTail(stringBuffer);
            stringBuffer.append("\n");
        }
        return stringBuffer.toString();
    }

    // 创建日期格式化对象
    private static final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd 00:00:00");

    // 创建数值格式化对象
    private static final DataFormatter dataFormatter = new DataFormatter();

    private static String getStringValue(Cell cell, FormulaEvaluator evaluator){
        // 获取单元格的数据类型
        CellType cellType = cell.getCellType();

        // 根据数据类型进行相应的处理
        switch (cellType) {
            case BLANK:
                // 处理空白单元格
                // ...
                break;
            case BOOLEAN:
                // 处理布尔类型单元格
                boolean booleanValue = cell.getBooleanCellValue();
                return booleanValue ? "是" : "否";
            case ERROR:
                // 处理错误类型单元格
                byte errorValue = cell.getErrorCellValue();
                break;
            case FORMULA:
                // 处理公式类型单元格
                CellValue formulaValue = evaluator.evaluate(cell);
                // 根据实际情况处理公式计算结果
                if (formulaValue.getCellType() == CellType.NUMERIC) {
                    return String.valueOf(formulaValue.getNumberValue());
                } else if (formulaValue.getCellType() == CellType.STRING) {
                    return formulaValue.getStringValue();
                }
                break;
            case NUMERIC:
                // 根据数据类型进行相应的处理
                if (cellType == CellType.NUMERIC) {
                    // 判断单元格样式是否为日期格式
                    if (DateUtil.isCellDateFormatted(cell)) {
                        // 处理日期类型单元格
                        java.util.Date dateValue = cell.getDateCellValue();
                        return dateFormat.format(dateValue);
                    } else {
                        // 处理数值类型单元格
                        return dataFormatter.formatCellValue(cell);
                    }
                }
                // 处理数值类型单元格
                return String.valueOf(cell.getNumericCellValue());
            case STRING:
                // 处理字符串类型单元格
                return cell.getStringCellValue();
            default:
                // 其他类型的处理
                break;
        }
        return "";
    }


    private static String convertToLetter(int number) {
        if (number < 0 || number >= 26) {
            throw new IllegalArgumentException("Number must be between 0 and 25");
        }
        return String.valueOf((char)('A' + number));
    }


    private static String convertToExcelColumn(int number) {
        if (number < 0) {
            throw new IllegalArgumentException("Number must not be negative");
        }

        StringBuilder column = new StringBuilder();

        while (number >= 0) {
            int remainder = number % 26;
            column.insert(0, (char) ('A' + remainder));
            number = (number / 26) - 1;
        }

        return column.toString();
    }
    
}
